Signed vs Unsigned Integer Types in MySQL
MySQL integer types (TINYINT, SMALLINT, INT, BIGINT) can be defined as SIGNED or UNSIGNED. This affects the allowed range of values, how MySQL evaluates comparisons, how indexes are used, and what happens during overflow.
SIGNED integers allow negative values, while UNSIGNED integers only store 0 and positive values.
UNSIGNED effectively doubles the maximum positive range (e.g., INT SIGNED max = 2,147,483,647 vs INT UNSIGNED max = 4,294,967,295).
Indexing works the same structurally, but the value distribution affects query selectivity and performance.
Comparisons behave differently when negative values are involved (SIGNED) vs always non-negative (UNSIGNED).
Storage size is identical between SIGNED and UNSIGNED versions of the same type.
Only the allowed numeric range differs; bytes used are the same.
Example: INT (SIGNED or UNSIGNED) always uses 4 bytes.
UNSIGNED indexes may provide better selectivity when the dataset contains large positive-only values.
Comparisons between SIGNED and UNSIGNED columns may cause implicit type conversion, reducing index usage.
Sorting differs because SIGNED sorts negatives first, while UNSIGNED always starts from 0.
Example: A SIGNED column can store −100 to 100, but UNSIGNED cannot store negative numbers; attempts will cause errors or wrap-around.
SIGNED TINYINT range: −128 to 127
UNSIGNED TINYINT range: 0 to 255
Inserting 130 into SIGNED TINYINT overflows and wraps to −126
Inserting −1 into UNSIGNED TINYINT becomes 255 (wrap-around)
Result: a = -126 and b = 255 due to wrapping behavior.